package com.jiepos.mpos.biz.customer.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.CustomAccountChangeDao;
import com.jiepos.mpos.common.entity.App_Custom_Account_ChangeEntity;
import com.jiepos.mpos.common.entity.App_Custom_InfEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 会员账户变动明细业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.customer.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年9月25日 下午4:36:57
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class CustomAccountChangeService extends BaseService {

	@Autowired
	private CustomAccountChangeDao customAccountChangeDao;

	/**
	 * 查询会员账户变动明细json数据
	 * @param request 
	 * 
	 * @param pagination
	 * @param customInfEntity
	 * @param customAccountChangeEntity
	 * @param account_type 
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map getCustomerAccountChangeList(HttpServletRequest request, Pagination pagination, App_Custom_InfEntity customInfEntity,
			App_Custom_Account_ChangeEntity customAccountChangeEntity, String account_type) {
		StringBuffer sb = getInitSql("t1").append(", ");
		sb.append("t2.custom_no, ");
		sb.append("CONCAT (RPAD (SUBSTR (t2.login_id, 1, 3),LENGTH (t2.login_id) - 4,'*'),SUBSTR (t2.login_id,LENGTH (t2.login_id) - 3,4))||'-'||t2.name as name, ");
		sb.append("(select code_val||'-'||code_desc from " + Table.T_APP_CODE_LIST + " where code_tp = '"
				+ DBConstant.Code.ACCOUNT_TYPE + "' and code_val = t3.account_type) as account_type, ");
		sb.append("to_char(t1.balance,'fm9999999990.00') as balance, ");
		sb.append(
				"case t1.directions when 'D' then '-'||to_char(t1.amount,'fm9999999990.00') else ''||to_char(t1.amount,'fm9999999990.00') end as amount, ");
		sb.append("to_char(t1.after_balance,'fm9999999990.00') as after_balance, ");
		sb.append("t1.loc_trace_no, ");
		sb.append("t1.remarks ");
		sb.append("from " + Table.T_APP_CUSTOM_ACCOUNT_CHANGE + " t1 ");
		sb.append("left join " + Table.T_APP_CUSTOM_INF + " t2 ");
		sb.append("on t1.custom_uuid = t2.custom_uuid ");
		sb.append("left join " + Table.T_APP_CUSTOM_ACCOUNT + " t3 ");
		sb.append("on t1.custom_account_uuid = t3.account_uuid ");
		sb.append("where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(customInfEntity.getPhone())) {
			sb.append("and t2.login_id like ? ");
			list.add("%" + customInfEntity.getPhone() + "%");
		}
		if (!StringUtils.isBlank(customInfEntity.getName())) {
			sb.append("and t2.name like ? ");
			list.add("%" + customInfEntity.getName() + "%");
		}
		if (!StringUtils.isBlank(customAccountChangeEntity.getLoc_trace_no())) {
			sb.append("and t1.loc_trace_no = ? ");
			list.add(customAccountChangeEntity.getLoc_trace_no());
		}
		if (!StringUtils.isBlank(customInfEntity.getCustom_no())) {
			sb.append("and t2.custom_no = ? ");
			list.add(customInfEntity.getCustom_no());
		}
		if (!StringUtils.isBlank(account_type)) {
			sb.append("and t3.account_type = ? ");
			list.add(account_type);
		}
		pagination.queryString = sb.toString();
		pagination = super.getCustomAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " and t1.rec_st!='"
				+ SystemConstant.DEL_STATUS + "' order by t1.rec_id desc";
 
		return customAccountChangeDao.getPaginationMap(pagination, "t1", list);
	}

	/**
	 * 查询会员佣金及分润
	 * @param custom_uuid 会员uuid
	 * @param dt 日期 yyyyMMdd
	 * @return
	 */
	public Map<String,String> getCommissionAndProfit(String custom_uuid,String dt){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT A.AMOUNT AS COMMISSION, B.AMOUNT AS PROFIT ")
				.append("   FROM (SELECT TO_CHAR(NVL(SUM(AMOUNT),0.00),'FM9999999990.00') AS AMOUNT ")
				.append("           FROM T_APP_CUSTOM_ACCOUNT_CHANGE ")
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0' ")
				.append("            AND LOC_DT = ? ")
				.append("            AND DIRECTIONS = 'C' ")
				.append("            AND CUSTOM_ACCOUNT_UUID IN ")
				.append("                (SELECT ACCOUNT_UUID ")
				.append("                   FROM T_APP_CUSTOM_ACCOUNT ")
				.append("                  WHERE CUSTOM_UUID = ? ")
				.append("                    AND ACCOUNT_TYPE IN ('2','3') ")
				.append("                    AND REC_ST != '0')) A, ")
				.append("        (SELECT TO_CHAR(NVL(SUM(AMOUNT),0.00),'FM9999999990.00') AS AMOUNT ")
				.append("           FROM T_APP_CUSTOM_ACCOUNT_CHANGE ")
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0' ")
				.append("            AND LOC_DT = ? ")
				.append("            AND DIRECTIONS = 'C' ")
				.append("            AND CUSTOM_ACCOUNT_UUID IN ")
				.append("                (SELECT ACCOUNT_UUID ")
				.append("                   FROM T_APP_CUSTOM_ACCOUNT ")
				.append("                  WHERE CUSTOM_UUID = ? ")
				.append("                    AND ACCOUNT_TYPE IN ('4') ")
				.append("                    AND REC_ST != '0')) B ");
		List<String> params = new ArrayList<>();
		params.add(custom_uuid);
		params.add(dt);
		params.add(custom_uuid);
		params.add(custom_uuid);
		params.add(dt);
		params.add(custom_uuid);
		List<Map<String,Object>> list = customAccountChangeDao.queryForList(sqlBuf.toString(), params);
		//结果集
		Map<String,String> resultMap = new HashMap<>();
		if(list != null && list.size() > 0){
			Map<String, Object> map = list.get(0);
			resultMap.put("COMMISSION",StringUtils.objToString(map.get("COMMISSION")));
			resultMap.put("PROFIT",StringUtils.objToString(map.get("PROFIT")));
		}else{
			resultMap.put("COMMISSION","0.00");
			resultMap.put("PROFIT","0.00");
		}
		return resultMap;
	}

	/**
	 * 查询会员累计佣金及分润
	 * @param custom_uuid 会员uuid
	 * @return
	 */
	public Map<String,String> getSumCommissionAndProfit(String custom_uuid){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT A.AMOUNT AS COMMISSION, B.AMOUNT AS PROFIT ")
				.append("   FROM (SELECT TO_CHAR(NVL(SUM(AMOUNT),0.00),'FM9999999990.00') AS AMOUNT ")
				.append("           FROM T_APP_CUSTOM_ACCOUNT_CHANGE ")
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0' ")
				.append("            AND DIRECTIONS = 'C' ")
				.append("            AND CUSTOM_ACCOUNT_UUID IN ")
				.append("                (SELECT ACCOUNT_UUID ")
				.append("                   FROM T_APP_CUSTOM_ACCOUNT ")
				.append("                  WHERE CUSTOM_UUID = ? ")
				.append("                    AND ACCOUNT_TYPE IN ('2','3') ")
				.append("                    AND REC_ST != '0')) A, ")
				.append("        (SELECT TO_CHAR(NVL(SUM(AMOUNT),0.00),'FM9999999990.00') AS AMOUNT ")
				.append("           FROM T_APP_CUSTOM_ACCOUNT_CHANGE ")
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0' ")
				.append("            AND DIRECTIONS = 'C' ")
				.append("            AND CUSTOM_ACCOUNT_UUID IN ")
				.append("                (SELECT ACCOUNT_UUID ")
				.append("                   FROM T_APP_CUSTOM_ACCOUNT ")
				.append("                  WHERE CUSTOM_UUID = ? ")
				.append("                    AND ACCOUNT_TYPE IN ('4') ")
				.append("                    AND REC_ST != '0')) B ");
		List<String> params = new ArrayList<>();
		params.add(custom_uuid);
		params.add(custom_uuid);
		params.add(custom_uuid);
		params.add(custom_uuid);
		List<Map<String,Object>> list = customAccountChangeDao.queryForList(sqlBuf.toString(), params);
		//结果集
		Map<String,String> resultMap = new HashMap<>();
		if(list != null && list.size() > 0){
			Map<String, Object> map = list.get(0);
			resultMap.put("COMMISSION",StringUtils.objToString(map.get("COMMISSION")));
			resultMap.put("PROFIT",StringUtils.objToString(map.get("PROFIT")));
		}else{
			resultMap.put("COMMISSION","0.00");
			resultMap.put("PROFIT","0.00");
		}
		return resultMap;
	}


	/**
	 * 根据会员UUID查询佣金流水
	 * @param custom_uuid
	 * @param begin_tm
	 * @param end_tm
	 * @param curr_page
	 * @param rows_perpage
	 * @return
	 */
	public List getCommissionFlowList(String custom_uuid, String begin_tm, String end_tm,String curr_page,String rows_perpage){
		Pagination pagination = new  Pagination();
		pagination.setPage(Integer.parseInt(curr_page));
		pagination.setRows(Integer.parseInt(rows_perpage));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT TO_CHAR(TO_DATE(A.LOC_DT || A.LOC_TS, 'yyyyMMddHH24miss'), ")
				.append("                'yyyy-MM-dd HH24:mi:ss') AS \"trans_tm\", ")
				.append("        SUBSTR(B.LOGIN_ID, 1, 3) || '****' || SUBSTR(B.LOGIN_ID, -4, 4) AS \"sponsor_login_id\", ")
				.append("        C.CODE_DESC AS \"balance_source\", ")
				.append("        A.SERVICE_NAME || '[' || A.PARAM_NAME || ']' AS \"service_name\", ")
				.append("        DECODE(A.DIRECTIONS, 'C', '+', 'D', '-') || ")
				.append("        TO_CHAR(NVL(A.AMOUNT, 0.00), 'fm9999999990.00') AS \"commission_amt\", ")
				.append("        A.REMARKS AS \"remarks\", ")
				.append("        D.ACCOUNT_TYPE AS \"account_type\" ")
				.append("   FROM (SELECT LOC_DT, ")
				.append("                LOC_TS, ")
				.append("                BALANCE_SOURCE, ")
				.append("                SERVICE_NAME, ")
				.append("                PARAM_NAME, ")
				.append("                AMOUNT, ")
				.append("                REMARKS, ")
				.append("                DIRECTIONS, ")
				.append("                CUSTOM_ACCOUNT_UUID, ")
				.append("                CUR_CUSTOM_UUID ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT_CHANGE)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0') A, ")
				.append("        (SELECT ACCOUNT_UUID, ")
				.append("                (SELECT CODE_DESC ")
				.append("                   FROM  "+Table.T_APP_CODE_LIST)
				.append("                  WHERE CODE_TP = '"+ DBConstant.Code.ACCOUNT_TYPE+"' ")
				.append("                    AND CODE_VAL = ACCOUNT_TYPE) AS ACCOUNT_TYPE ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND ACCOUNT_TYPE IN ('2', '3') ")
				.append("            AND REC_ST != '0') D, ")
				.append("        "+ Table.T_APP_CUSTOM_INF+" B, ")
				.append("        (SELECT CODE_VAL, CODE_DESC ")
				.append("           FROM  "+Table.T_APP_CODE_LIST)
				.append("          WHERE CODE_TP = '"+DBConstant.Code.BALANCE_SOURCE+"') C ")
				.append("  WHERE A.CUR_CUSTOM_UUID = B.CUSTOM_UUID ")
				.append("    AND A.BALANCE_SOURCE = C.CODE_VAL ")
				.append("    AND A.CUSTOM_ACCOUNT_UUID = D.ACCOUNT_UUID ");
		List params = new ArrayList();
		params.add(custom_uuid);
		params.add(custom_uuid);
		if (!StringUtils.isBlank(begin_tm)) {
			sqlBuf.append("AND A.LOC_DT >= ? ");
			params.add(begin_tm);
		}
		if (!StringUtils.isBlank(end_tm)) {
			sqlBuf.append("AND A.LOC_DT <= ? ");
			params.add(end_tm);
		}
		sqlBuf.append(" ORDER BY A.LOC_DT DESC, A.LOC_TS DESC ");

		pagination.queryString = sqlBuf.toString();
		pagination = customAccountChangeDao.getPagination(pagination, "A", params);
		List<Map<String,String>> resultList =  pagination.getOldData();
		return resultList;
	}

	/**
	 * 根据会员UUID查询分润流水
	 * @param custom_uuid
	 * @param begin_tm
	 * @param end_tm
	 * @param curr_page
	 * @param rows_perpage
	 * @return
	 */
	public List getProfitFlowList(String custom_uuid, String begin_tm, String end_tm,String curr_page,String rows_perpage){
		Pagination pagination = new  Pagination();
		pagination.setPage(Integer.parseInt(curr_page));
		pagination.setRows(Integer.parseInt(rows_perpage));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT TO_CHAR(TO_DATE(A.LOC_DT || A.LOC_TS, 'yyyyMMddHH24miss'), ")
				.append("                'yyyy-MM-dd HH24:mi:ss') AS \"trans_tm\", ")
				.append("        SUBSTR(B.LOGIN_ID, 1, 3) || '****' || SUBSTR(B.LOGIN_ID, -4, 4) AS \"sponsor_login_id\", ")
				.append("        C.CODE_DESC AS \"balance_source\", ")
				.append("        A.SERVICE_NAME || '[' || A.PARAM_NAME || ']' AS \"service_name\", ")
				.append("        DECODE(A.DIRECTIONS, 'C', '+', 'D', '-') || ")
				.append("        TO_CHAR(NVL(A.AMOUNT, 0.00), 'fm9999999990.00') AS \"commission_amt\", ")
				.append("        A.REMARKS AS \"remarks\", ")
				.append("        D.ACCOUNT_TYPE AS \"account_type\" ")
				.append("   FROM (SELECT LOC_DT, ")
				.append("                LOC_TS, ")
				.append("                BALANCE_SOURCE, ")
				.append("                SERVICE_NAME, ")
				.append("                PARAM_NAME, ")
				.append("                AMOUNT, ")
				.append("                REMARKS, ")
				.append("                DIRECTIONS, ")
				.append("                CUSTOM_ACCOUNT_UUID, ")
				.append("                CUR_CUSTOM_UUID ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT_CHANGE)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0') A, ")
				.append("        (SELECT ACCOUNT_UUID, ")
				.append("                (SELECT CODE_DESC ")
				.append("                   FROM  "+Table.T_APP_CODE_LIST)
				.append("                  WHERE CODE_TP = '"+ DBConstant.Code.ACCOUNT_TYPE+"' ")
				.append("                    AND CODE_VAL = ACCOUNT_TYPE) AS ACCOUNT_TYPE ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND ACCOUNT_TYPE IN ('4') ")
				.append("            AND REC_ST != '0') D, ")
				.append("        "+ Table.T_APP_CUSTOM_INF+" B, ")
				.append("        (SELECT CODE_VAL, CODE_DESC ")
				.append("           FROM  "+Table.T_APP_CODE_LIST)
				.append("          WHERE CODE_TP = '"+DBConstant.Code.BALANCE_SOURCE+"') C ")
				.append("  WHERE A.CUR_CUSTOM_UUID = B.CUSTOM_UUID ")
				.append("    AND A.BALANCE_SOURCE = C.CODE_VAL ")
				.append("    AND A.CUSTOM_ACCOUNT_UUID = D.ACCOUNT_UUID ");
		List params = new ArrayList();
		params.add(custom_uuid);
		params.add(custom_uuid);
		if (!StringUtils.isBlank(begin_tm)) {
			sqlBuf.append("AND A.LOC_DT >= ? ");
			params.add(begin_tm);
		}
		if (!StringUtils.isBlank(end_tm)) {
			sqlBuf.append("AND A.LOC_DT <= ? ");
			params.add(end_tm);
		}
		sqlBuf.append(" ORDER BY A.LOC_DT DESC, A.LOC_TS DESC ");

		pagination.queryString = sqlBuf.toString();
		pagination = customAccountChangeDao.getPagination(pagination, "A", params);
		List<Map<String,String>> resultList =  pagination.getOldData();
		return resultList;
	}


	/**
	 * 查询账户变动数据(流水)
	 * @param custom_uuid
	 * @param begin_tm
	 * @param end_tm
	 * @param curr_page
	 * @param rows_perpage
	 * @return
	 */
	public List getAccountChangeList(String custom_uuid, String begin_tm, String end_tm,String curr_page,String rows_perpage){
		Pagination pagination = new  Pagination();
		pagination.setPage(Integer.parseInt(curr_page));
		pagination.setRows(Integer.parseInt(rows_perpage));
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append(" SELECT TO_CHAR(TO_DATE(A.LOC_DT || A.LOC_TS, 'yyyyMMddHH24miss'), ")
				.append("                'yyyy-MM-dd HH24:mi:ss') AS \"trans_tm\", ")
				.append("        A.SERVICE_NAME || '[' || A.PARAM_NAME || ']' AS \"service_name\", ")
				.append("        DECODE(A.DIRECTIONS, 'C', '+', 'D', '-') || ")
				.append("        TO_CHAR(NVL(A.AMOUNT, 0.00), 'fm9999999990.00') AS \"change_amt\", ")
				.append("        TO_CHAR(NVL(A.AFTER_BALANCE, 0.00), 'fm9999999990.00') AS \"after_balance\", ")
				.append("        C.CODE_DESC AS \"balance_source\", ")
				.append("        D.ACCOUNT_TYPE AS \"account_type\", ")
				.append("        A.REMARKS AS \"remarks\" ")
				.append("   FROM (SELECT LOC_DT, ")
				.append("                LOC_TS, ")
				.append("                AFTER_BALANCE, ")
				.append("                BALANCE_SOURCE, ")
				.append("                SERVICE_NAME, ")
				.append("                PARAM_NAME, ")
				.append("                AMOUNT, ")
				.append("                REMARKS, ")
				.append("                DIRECTIONS, ")
				.append("                CUSTOM_ACCOUNT_UUID ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT_CHANGE)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0') A, ")
				.append("        (SELECT ACCOUNT_UUID, ")
				.append("                (SELECT CODE_DESC ")
				.append("                   FROM  "+Table.T_APP_CODE_LIST)
				.append("                  WHERE CODE_TP = '"+ DBConstant.Code.ACCOUNT_TYPE+"' ")
				.append("                    AND CODE_VAL = ACCOUNT_TYPE) AS ACCOUNT_TYPE ")
				.append("           FROM  "+Table.T_APP_CUSTOM_ACCOUNT)
				.append("          WHERE CUSTOM_UUID = ? ")
				.append("            AND REC_ST != '0') D, ")
				.append("        (SELECT CODE_VAL, CODE_DESC ")
				.append("           FROM  "+Table.T_APP_CODE_LIST)
				.append("          WHERE CODE_TP = '"+DBConstant.Code.BALANCE_SOURCE+"') C ")
				.append("  WHERE A.BALANCE_SOURCE = C.CODE_VAL ")
				.append("    AND A.CUSTOM_ACCOUNT_UUID = D.ACCOUNT_UUID ");
		List params = new ArrayList();
		params.add(custom_uuid);
		params.add(custom_uuid);
		if (!StringUtils.isBlank(begin_tm)) {
			sqlBuf.append("AND A.LOC_DT >= ? ");
			params.add(begin_tm);
		}
		if (!StringUtils.isBlank(end_tm)) {
			sqlBuf.append("AND A.LOC_DT <= ? ");
			params.add(end_tm);
		}
		sqlBuf.append(" order by A.LOC_DT DESC, A.LOC_TS DESC ");

		pagination.queryString = sqlBuf.toString();
		pagination = customAccountChangeDao.getPagination(pagination, "A", params);
		List<Map<String,String>> resultList =  pagination.getOldData();
		return resultList;
	}


}
